package com.java2007.dao.impl;

import com.java2007.dao.TableDao;
import com.java2007.pojo.Table;
import com.java2007.util.JdbcUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.List;


public class TableDaoImpl implements TableDao {
    /**
     * 查询所有
     * @return
     */
    @Override
    public List<Table> selectAll() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "SELECT table_id tableId,table_name tableName, table_status tableStatus,reserve_time reserveTime, "
                + "table_create_time tableCreateTime, table_update_time tableUpdateTime , user_id userId FROM t_dinner_table";
        List<Table> query = queryRunner.query(sql, new BeanListHandler<>(Table.class));
        return query;
    }


    /**
     * 通过餐桌名字查询
     * @param keyword
     * @return
     */
    @Override
    public List<Table> findByTableName(String keyword) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "SELECT table_id tableId,table_name tableName, table_status tableStatus,reserve_time reserveTime, " +
                "table_create_time tableCreateTime, table_update_time tableUpdateTime , user_id userId FROM t_dinner_table WHERE table_name LIKE ?";
        List<Table> query = queryRunner.query(sql, new BeanListHandler<>(Table.class), keyword);
        return query;
    }

    /**
     * 增加餐桌
     * @param table
     * @return
     */
    @Override
    public int insertTable(Table table) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "INSERT INTO t_dinner_table(table_name,table_status,table_create_time,table_update_time,user_id) VALUES(?,0,?,?,?)";
        int update = queryRunner.update(sql, table.getTableName(), table.getTableCreateTime(), table.getTableUpdateTime(), table.getUserId());
        return update;
    }

    /**
     * 预定/退桌
     * @param table
     * @return
     */
    @Override
    public int updateById(Table table) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "UPDATE t_dinner_table SET table_status =?,reserve_time=? WHERE table_id = ?";
        int update = queryRunner.update(sql, table.getTableStatus(), table.getReserveTime(), table.getTableId());
        return update;
    }

    /**
     * 通过tableId查询
     * @param tableId
     * @return
     */
    @Override
    public Table findByTableId(Integer tableId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "SELECT table_id tableId,table_name tableName, table_status tableStatus,reserve_time reserveTime, " +
                "table_create_time tableCreateTime, table_update_time tableUpdateTime , user_id userId FROM t_dinner_table WHERE table_id = ?";
        Table query = queryRunner.query(sql, new BeanHandler<>(Table.class), tableId);
        return query;
    }

    /**
     * 根据tableId删除
     * @param tableId
     * @return
     */
    @Override
    public int deleteById(Integer tableId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "DELETE FROM t_dinner_table WHERE table_id = ?";
        int update = queryRunner.update(sql, tableId);
        return update;
    }

    /**
     * 查询空闲的桌子
     * @return
     */
    @Override
    public List<Table> findByTableStatus() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "SELECT table_id tableId,table_name tableName, table_status tableStatus,reserve_time reserveTime, " +
                "table_create_time tableCreateTime, table_update_time tableUpdateTime , user_id userId FROM t_dinner_table WHERE table_status = ?";;
        List<Table> query = queryRunner.query(sql, new BeanListHandler<>(Table.class),0);
        return query;
    }
}
